﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Physis.Core.Insurer {
    
    public class InsurerFactory : FactoryBase {

        #region Constructors

        public InsurerFactory (Application forApplication) : base (forApplication) { return; }

        #endregion 
        

        #region Factories

        public List<Insurer> Insurers (Boolean useCaching = false) {

            List<Insurer> insurers = new List<Insurer> ();

            String cacheKey = "Application.Core.Insurer.All"; 

            ClearLastException ();

            try {

                if (useCaching) { insurers = (List<Insurer>)CacheManager.GetObject (cacheKey); }

                else { insurers = null; }

                if (insurers == null) {

                    insurers = new List<Core.Insurer.Insurer> ();

                    String selectStatement = "SELECT * FROM Insurer JOIN Entity ON Insurer.EntityId = Entity.Id ORDER BY Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        Insurer insurer = new Insurer (Application);

                        insurer.MapDataFields (currentDataRow);

                        insurers.Add (insurer);

                    }

                    CacheManager.CacheObject (cacheKey, insurers, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return insurers;

        }
        
        public Insurer Insurer (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.Insurer." + id;

            Insurer insurer = null;

            ClearLastException ();

            try {

                if (useCaching) { insurer = (Insurer)CacheManager.GetObject (cacheKey); }

                if (insurer == null) {

                    insurer = new Insurer (Application, id);

                    CacheManager.CacheObject (cacheKey, insurer, CacheExpirationData);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return insurer;

        }


        public List<ProgramType> ProgramTypes (Boolean useCaching = false) {

            List<ProgramType> programTypes = new List<ProgramType> ();

            String cacheKey = "Application.ProgramTypes.All";

            ClearLastException ();

            try {

                if (useCaching) { programTypes = (List<ProgramType>)CacheManager.GetObject (cacheKey); }

                else { programTypes = null; }

                if (programTypes == null) {

                    programTypes = new List<ProgramType> ();

                    String selectStatement = "SELECT ProgramType.* FROM ProgramType ORDER BY ProgramType.Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        ProgramType programType = new ProgramType (Application);

                        programType.MapDataFields (currentDataRow);

                        CacheManager.CacheObject ("Application.ProgramType." + programType.Id, programType, CacheExpirationReference);  // CACHE INDIVIDUAL ROLE 

                        programTypes.Add (programType);

                    }

                    CacheManager.CacheObject (cacheKey, programTypes, CacheExpirationReference); // CACHE THE WHOLE LIST

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return programTypes;

        }

        public ProgramType ProgramType (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.ProgramType." + id;

            ProgramType programType = null;

            ClearLastException ();

            try {

                if (useCaching) { programType = (ProgramType)CacheManager.GetObject (cacheKey); }

                if (programType == null) {

                    programType = new ProgramType (Application, id);

                    CacheManager.CacheObject (cacheKey, programType, CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return programType;

        }

        public ProgramType ProgramType (String name, Boolean useCaching = false) {

            if (String.IsNullOrWhiteSpace (name)) { return null; }

            String cacheKey = "Physis.Application.ProgramType." + name;

            ProgramType programType = null;

            ClearLastException ();

            try {

                if (useCaching) { programType = (ProgramType)CacheManager.GetObject (cacheKey); }

                if (programType == null) {

                    programType = new ProgramType (Application, name);

                    CacheManager.CacheObject (cacheKey, programType, CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return programType;

        }


        public List<Program> Programs (Boolean useCaching = false) {

            List<Program> programs = new List<Program> ();

            String cacheKey = "Application.Core.Program.All";

            ClearLastException ();

            try {

                if (useCaching) { programs = (List<Program>)CacheManager.GetObject (cacheKey); }

                else { programs = null; }

                if (programs == null) {

                    programs = new List<Program> ();

                    String selectStatement = "SELECT * FROM Program ORDER BY Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        Program program = new Program (Application);

                        program.MapDataFields (currentDataRow);

                        programs.Add (program);

                    }

                    CacheManager.CacheObject (cacheKey, programs, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return programs;

        }

        public Program Program (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.Program." + id;

            Program program = null;

            ClearLastException ();

            try {

                if (useCaching) { program = (Program)CacheManager.GetObject (cacheKey); }

                if (program == null) {

                    program = new Program (Application, id);

                    CacheManager.CacheObject (cacheKey, program, CacheExpirationData);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return program;

        }


        public List<InsuranceType> InsuranceTypes (Boolean useCaching = false) {

            List<InsuranceType> insuranceTypes = new List<InsuranceType> ();

            String cacheKey = "Application.InsuranceTypes.All";

            ClearLastException ();

            try {

                if (useCaching) { insuranceTypes = (List<InsuranceType>)CacheManager.GetObject (cacheKey); }

                else { insuranceTypes = null; }

                if (insuranceTypes == null) {

                    insuranceTypes = new List<InsuranceType> ();

                    String selectStatement = "SELECT InsuranceType.*  FROM InsuranceType ORDER BY InsuranceType.Name";


                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        InsuranceType insuranceType = new InsuranceType (Application);

                        insuranceType.MapDataFields (currentDataRow);

                        CacheManager.CacheObject ("Application.InsuranceType." + insuranceType.Id, insuranceType, CacheExpirationReference);  // CACHE INDIVIDUAL ROLE 

                        insuranceTypes.Add (insuranceType);

                    }

                    CacheManager.CacheObject (cacheKey, insuranceTypes, CacheExpirationReference); // CACHE THE WHOLE LIST

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return insuranceTypes;

        }

        public InsuranceType InsuranceType (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.InsuranceType." + id;

            InsuranceType insuranceType = null;

            ClearLastException ();

            try {

                if (useCaching) { insuranceType = (InsuranceType)CacheManager.GetObject (cacheKey); }

                if (insuranceType == null) {

                    insuranceType = new InsuranceType (Application, id);

                    CacheManager.CacheObject (cacheKey, insuranceType, CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return insuranceType;

        }

        public InsuranceType InsuranceType (String name, Boolean useCaching = false) {

            if (String.IsNullOrWhiteSpace (name)) { return null; }

            String cacheKey = "Physis.Application.InsuranceType." + name;

            InsuranceType insuranceType = null;

            ClearLastException ();

            try {

                if (useCaching) { insuranceType = (InsuranceType)CacheManager.GetObject (cacheKey); }

                if (insuranceType == null) {

                    insuranceType = new InsuranceType (Application, name);

                    CacheManager.CacheObject (cacheKey, insuranceType, CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return insuranceType;

        }


        public List<BenefitPlan> BenefitPlans (Boolean useCaching = false) {

            List<BenefitPlan> benefitPlans = new List<BenefitPlan> ();

            String cacheKey = "Application.Core.BenefitPlan.All";

            ClearLastException ();

            try {

                if (useCaching) { benefitPlans = (List<BenefitPlan>)CacheManager.GetObject (cacheKey); }

                else { benefitPlans = null; }

                if (benefitPlans == null) {

                    benefitPlans = new List<BenefitPlan> ();

                    String selectStatement = "SELECT * FROM BenefitPlan ORDER BY Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        BenefitPlan benefitPlan = new BenefitPlan (Application);

                        benefitPlan.MapDataFields (currentDataRow);

                        benefitPlans.Add (benefitPlan);

                    }

                    CacheManager.CacheObject (cacheKey, benefitPlans, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return benefitPlans;

        }

        public List<BenefitPlan> BenefitPlansByProgram (Int64 programId, Boolean useCaching = false) {

            List<BenefitPlan> benefitPlans = new List<BenefitPlan> ();

            String cacheKey = "Application.Core.BenefitPlan.Program." + programId;

            ClearLastException ();

            try {

                if (useCaching) { benefitPlans = (List<BenefitPlan>)CacheManager.GetObject (cacheKey); }

                else { benefitPlans = null; }

                if (benefitPlans == null) {

                    benefitPlans = new List<BenefitPlan> ();

                    String selectStatement = "SELECT * FROM BenefitPlan WHERE ProgramId = " + programId + " ORDER BY Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        BenefitPlan benefitPlan = new BenefitPlan (Application);

                        benefitPlan.MapDataFields (currentDataRow);

                        benefitPlans.Add (benefitPlan);

                    }

                    CacheManager.CacheObject (cacheKey, benefitPlans, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return benefitPlans;

        }

        public BenefitPlan BenefitPlan (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.BenefitPlan." + id;

            BenefitPlan benefitPlan = null;

            ClearLastException ();

            try {

                if (useCaching) { benefitPlan = (BenefitPlan)CacheManager.GetObject (cacheKey); }

                if (benefitPlan == null) {

                    benefitPlan = new BenefitPlan (Application, id);

                    CacheManager.CacheObject (cacheKey, benefitPlan, CacheExpirationData);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return benefitPlan;

        }
        

        

        public List<Rider> Riders (Boolean useCaching = false) {

            List<Rider> riders = new List<Rider> ();

            String cacheKey = "Application.Core.Rider.All";

            ClearLastException ();

            try {

                if (useCaching) { riders = (List<Rider>)CacheManager.GetObject (cacheKey); }

                else { riders = null; }

                if (riders == null) {

                    riders = new List<Rider> ();

                    String selectStatement = "SELECT * FROM Rider ORDER BY Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        Rider rider = new Rider (Application);

                        rider.MapDataFields (currentDataRow);

                        riders.Add (rider);

                    }

                    CacheManager.CacheObject (cacheKey, riders, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return riders;

        }

        public Rider Rider (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.Rider." + id;

            Rider rider = null;

            ClearLastException ();

            try {

                if (useCaching) { rider = (Rider)CacheManager.GetObject (cacheKey); }

                if (rider == null) {

                    rider = new Rider (Application, id);

                    CacheManager.CacheObject (cacheKey, rider, CacheExpirationData);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return rider;

        }


        public List<Product> Products (Boolean useCaching = false) {

            List<Product> products = new List<Product> ();

            String cacheKey = "Application.Core.Product.All";

            ClearLastException ();

            try {

                if (useCaching) { products = (List<Product>)CacheManager.GetObject (cacheKey); }

                else { products = null; }

                if (products == null) {

                    products = new List<Product> ();

                    String selectStatement = "SELECT * FROM Product ORDER BY Name";

                    System.Data.DataTable dataTable = Application.EnvironmentDatabase.SelectDataTable (selectStatement);

                    foreach (System.Data.DataRow currentDataRow in dataTable.Rows) {

                        Product product = new Product (Application);

                        product.MapDataFields (currentDataRow);

                        products.Add (product);

                    }

                    CacheManager.CacheObject (cacheKey, products, Application.CacheExpirationReference);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return products;

        }

        public Product Product (Int64 id, Boolean useCaching = false) {

            if (id == 0) { return null; }

            String cacheKey = "Physis.Application.Product." + id;

            Product product = null;

            ClearLastException ();

            try {

                if (useCaching) { product = (Product)CacheManager.GetObject (cacheKey); }

                if (product == null) {

                    product = new Product (Application, id);

                    CacheManager.CacheObject (cacheKey, product, CacheExpirationData);

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return product;

        }

        #endregion 
        

        #region Insurer Query

        private String InsurersQuerySqlStatement (List<Data.FilterDescriptor> filters) {

            String sqlStatement = String.Empty;


            String selectClause = String.Empty;

            String fromClause = String.Empty;

            String joinStatement = String.Empty;

            String whereClause = String.Empty;

            String orderByClause = String.Empty;



            selectClause = "SELECT Insurer.* /*_CUSTOM_FIELD_INSERT_*/ ";

            selectClause = selectClause + "\r\n /*_CALCULATED_FIELDS_BEGIN_*/ ";

            selectClause = selectClause + "\r\n /*_CALCULATED_FIELDS_END_*/ ";


            fromClause = "  FROM Insurer \r\n /*_CUSTOM_FILTER_JOINS_INSERT_*/";

            whereClause = "  WHERE (1 = 1) /*_CUSTOM_FILTER_INSERT_*/";


            if (filters == null) { filters = new List<Data.FilterDescriptor> (); }

            foreach (Data.FilterDescriptor currentFilter in filters) {

                String criteriaString = String.Empty;

                switch (currentFilter.PropertyPath.ToLower ()) {

                    case "name":

                        // DETERMINE IF WE ARE ALREADY JOINING TO THE ENTITY TABLE FOR THE ADDITIONAL FILTERS

                        if (!fromClause.Contains ("JOIN Entity")) {

                            fromClause += "    JOIN Entity ON Insurer.EntityId = Entity.Id \r\n";

                        }

                        criteriaString = currentFilter.SqlCriteriaString ("Entity");

                        break;

                    default:

                        if (currentFilter.PropertyPath.Split ('.').Length > 1) {

                            #region Related Object Properties

                            // HEIRARCHY FILTER CRITERIA 

                            switch (currentFilter.PropertyPath.ToLower ()) {

                                default: break; // PLACE HOLDER DO NOTHING

                            }

                            #endregion

                        }

                        else { criteriaString = currentFilter.SqlCriteriaString ("Insurer"); } // ASSUMES DEFAULT TABLE FILTERING

                        break;

                } // switch (currentFilter.PropertyPath) {

                if (!String.IsNullOrEmpty (criteriaString)) {

                    whereClause = whereClause + "  AND " + criteriaString;

                }

            }


            sqlStatement = selectClause + fromClause + joinStatement + whereClause + orderByClause;

            return sqlStatement;

        }

        private String InsurersQueryRowNumberSql (List<Data.SortDescriptor> sorts) {

            String sqlString = String.Empty;


            // CUSTOM SORTS TAKE PRECEDENCE OVER DEFAULT SORT

            if (sorts == null) { sorts = new List<Data.SortDescriptor> (); }

            foreach (Data.SortDescriptor currentSort in sorts) {

                switch (currentSort.FieldName.ToLower ()) {

                    default: sqlString = sqlString + currentSort.SqlSortList + ", "; break;

                }

            }


            // DEFAULT SORT 

            // APPEND MEMBER NUMBER AS ALWAYS LAST SORT OPTION

            sqlString = sqlString + "    Insurer.Id \r\n  ";


            sqlString = "    ROW_NUMBER () OVER (ORDER BY \r\n  " + sqlString;

            sqlString = sqlString + ") AS RowNumber, \r\n  ";

            return sqlString;

        }

        private String InsurersQueryJoinSqlForSort (List<Data.SortDescriptor> sorts) {

            String joinStatement = String.Empty;


            if (sorts == null) { sorts = new List<Data.SortDescriptor> (); }


            // IDENTIFY ALL RELATED TABLES REQUIRED 

            foreach (Data.SortDescriptor currentSort in sorts) {

                switch (currentSort.FieldName.Split ('.')[0].ToLower ()) {

                    default: break; // PLACE HOLDER DO NOTHING

                }

            }

            return joinStatement;

        }

        public Int32 InsurersCount (List<Data.FilterDescriptor> filters) {

            Int32 itemCount = 0;


            SetLastException (null);

            try {

                String selectStatement = "SELECT COUNT (1) AS CountOf FROM (" + InsurersQuerySqlStatement (filters) + ") SourceTable";

                itemCount = Convert.ToInt32 (Application.EnvironmentDatabase.ExecuteScalar (selectStatement));

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }


            return itemCount;

        }

        public List<Insurer> InsurersByPage (List<Data.FilterDescriptor> filters, List<Data.SortDescriptor> sorts, Int32 initialRow, Int32 count) {

            String cacheKey = "Application.Insurer.Id.";

            List<Insurer> items = new List<Insurer> ();

            System.Data.DataTable itemTable;

            String sqlStatement = String.Empty;

            String customFilters = String.Empty;

            String customFields = String.Empty;


            ClearLastException ();

            try {

                sqlStatement = sqlStatement + "SELECT InsurerPage.*, Entity.* FROM ( \r\n";

                sqlStatement = sqlStatement + "SELECT \r\n ";

                sqlStatement = sqlStatement + InsurersQueryRowNumberSql (sorts);

                sqlStatement = sqlStatement + "    Insurer.* \r\n"; // ADD COMMA IF EXTENDING DATA FIELDS 

                sqlStatement = sqlStatement + "  FROM (" + InsurersQuerySqlStatement (filters) + ") Insurer \r\n";

                // ADD JOINS THAT ARE REQUIRED FOR THE SORTING PARAMETERS

                sqlStatement = sqlStatement + InsurersQueryJoinSqlForSort (sorts);

                sqlStatement = sqlStatement + ") InsurerPage \r\n";

                sqlStatement = sqlStatement + "  JOIN Entity ON InsurerPage.EntityId = Entity.Id \r\n"; // THIS IS TO BRING  IN THE ENTITY DATA FIELDS FOR MAPPING


                sqlStatement = sqlStatement + "  WHERE InsurerPage.RowNumber BETWEEN " + initialRow.ToString ();

                sqlStatement = sqlStatement + " AND (" + initialRow.ToString () + " + " + count.ToString () + " - 1)";


                itemTable = Application.EnvironmentDatabase.SelectDataTable (sqlStatement);

                foreach (System.Data.DataRow currentRow in itemTable.Rows) {

                    Insurer item = new Core.Insurer.Insurer (Application);

                    item.MapDataFields (currentRow);

                    items.Add (item);


                    if (item != null) { // CACHE UNDER ID AND NUMBER WHEN EXISTS

                        CacheManager.CacheObject (cacheKey + item.Id, item, CacheExpirationReference);

                        CacheManager.CacheObject ("Application.Insurer.Id." + item.Id.ToString (), item, CacheExpirationReference);

                    }

                }

            }

            catch (Exception applicationException) {

                SetLastException (applicationException);

            }

            return items;

        }

        #endregion 

    }

}
